con <- dbConnect(
RMariaDB::MariaDB(),
host = "relational.fit.cvut.cz",
port = 3306,
username = "guest",
password = "relational",
dbname = "sakila"
)
dbListTables(con)
## [1] "actor" "address" "category" "city"
## [5] "country" "customer" "film" "film_actor"
## [9] "film_category" "film_text" "inventory" "language"
## [13] "payment" "rental" "staff" "store"
Q1
SELECT
customer.first_name,
customer.last_name,
customer.email,
COUNT(rental.rental_id) AS num_film_rentals
FROM
customer
JOIN rental ON customer.customer_id = rental.customer_id
GROUP BY
customer.customer_id, customer.first_name, customer.last_name, customer.email
ORDER BY
num_film_rentals DESC
LIMIT 10;
Q2
SELECT
film.title,
film.description
FROM
film
LEFT JOIN inventory ON film.film_id = inventory.film_id
LEFT JOIN rental ON inventory.inventory_id = rental.inventory_id
WHERE
rental.rental_id IS NULL
ORDER BY
film.title;
Displaying records 1 - 10
| ACADEMY DINOSAUR |
A Epic Drama of a Feminist And a Mad Scientist who must
Battle a Teacher in The Canadian Rockies |
| ALICE FANTASIA |
A Emotional Drama of a A Shark And a Database
Administrator who must Vanquish a Pioneer in Soviet Georgia |
| APOLLO TEEN |
A Action-Packed Reflection of a Crocodile And a
Explorer who must Find a Sumo Wrestler in An Abandoned Mine Shaft |
| ARGONAUTS TOWN |
A Emotional Epistle of a Forensic Psychologist And a
Butler who must Challenge a Waitress in An Abandoned Mine Shaft |
| ARK RIDGEMONT |
A Beautiful Yarn of a Pioneer And a Monkey who must
Pursue a Explorer in The Sahara Desert |
| ARSENIC INDEPENDENCE |
A Fanciful Documentary of a Mad Cow And a Womanizer who
must Find a Dentist in Berlin |
| BOONDOCK BALLROOM |
A Fateful Panorama of a Crocodile And a Boy who must
Defeat a Monkey in The Gulf of Mexico |
| BUTCH PANTHER |
A Lacklusture Yarn of a Feminist And a Database
Administrator who must Face a Hunter in New Orleans |
| CATCH AMISTAD |
A Boring Reflection of a Lumberjack And a Feminist who
must Discover a Woman in Nigeria |
| CHINATOWN GLADIATOR |
A Brilliant Panorama of a Technical Writer And a
Lumberjack who must Escape a Butler in Ancient India |
Q3
SELECT
category.name AS category,
AVG(film.length) AS avg_length
FROM
category
JOIN film_category ON category.category_id = film_category.category_id
JOIN film ON film_category.film_id = film.film_id
GROUP BY
category.category_id, category.name
ORDER BY
avg_length DESC;
Displaying records 1 - 10
| Sports |
128.2027 |
| Games |
127.8361 |
| Foreign |
121.6986 |
| Drama |
120.8387 |
| Comedy |
115.8276 |
| Family |
114.7826 |
| Music |
113.6471 |
| Travel |
113.3158 |
| Horror |
112.4821 |
| Classics |
111.6667 |
Q4
SELECT
actor.actor_id,
actor.first_name,
actor.last_name,
COUNT(film_actor.film_id) AS film_count
FROM
actor
JOIN film_actor ON actor.actor_id = film_actor.actor_id
GROUP BY
actor.actor_id, actor.first_name, actor.last_name
ORDER BY
film_count DESC
LIMIT 5;
5 records
| 107 |
GINA |
DEGENERES |
42 |
| 102 |
WALTER |
TORN |
41 |
| 198 |
MARY |
KEITEL |
40 |
| 181 |
MATTHEW |
CARREY |
39 |
| 23 |
SANDRA |
KILMER |
37 |
Q5
SELECT DISTINCT
customer.first_name,
customer.last_name
FROM
customer
JOIN rental ON customer.customer_id = rental.customer_id
JOIN inventory ON rental.inventory_id = inventory.inventory_id
JOIN film_actor ON inventory.film_id = film_actor.film_id
JOIN actor ON film_actor.actor_id = actor.actor_id
WHERE
actor.first_name = 'Johnny' AND actor.last_name = 'Depp';
Q6
SELECT
film.title,
SUM(payment.amount) AS total_revenue
FROM
film
JOIN inventory ON film.film_id = inventory.film_id
JOIN rental ON inventory.inventory_id = rental.inventory_id
JOIN payment ON rental.rental_id = payment.rental_id
GROUP BY
film.title
ORDER BY
total_revenue DESC
LIMIT 10;
Displaying records 1 - 10
| TELEGRAPH VOYAGE |
231.73 |
| WIFE TURN |
223.69 |
| ZORRO ARK |
214.69 |
| GOODFELLAS SALUTE |
209.69 |
| SATURDAY LAMBS |
204.72 |
| TITANS JERK |
201.71 |
| TORQUE BOUND |
198.72 |
| HARRY IDAHO |
195.70 |
| INNOCENT USUAL |
191.74 |
| HUSTLER PARTY |
190.78 |
dbDisconnect(con)
PLOTLY
Q1
options(repos = c(CRAN = "https://cran.rstudio.com"))
install.packages("plotly")
##
## The downloaded binary packages are in
## /var/folders/8_/x_11_k6n63x968_lr6dggkt80000gn/T//RtmpqTiAdv/downloaded_packages
library(plotly)
## Loading required package: ggplot2
##
## Attaching package: 'plotly'
## The following object is masked from 'package:ggplot2':
##
## last_plot
## The following object is masked from 'package:stats':
##
## filter
## The following object is masked from 'package:graphics':
##
## layout
plot <- plot_ly(data = diamonds, x = ~carat, y = ~price, mode = "markers", trace = "scatter")
plot <- plot %>% layout(
title = "Scatterplot of Carat vs. Price",
xaxis = list(title = "Carat"),
yaxis = list(title = "Price")
)
plot
## No trace type specified:
## Based on info supplied, a 'scatter' trace seems appropriate.
## Read more about this trace type -> https://plotly.com/r/reference/#scatter
## Warning: 'scatter' objects don't have these attributes: 'trace'
## Valid attributes include:
## 'cliponaxis', 'connectgaps', 'customdata', 'customdatasrc', 'dx', 'dy', 'error_x', 'error_y', 'fill', 'fillcolor', 'fillpattern', 'groupnorm', 'hoverinfo', 'hoverinfosrc', 'hoverlabel', 'hoveron', 'hovertemplate', 'hovertemplatesrc', 'hovertext', 'hovertextsrc', 'ids', 'idssrc', 'legendgroup', 'legendgrouptitle', 'legendrank', 'line', 'marker', 'meta', 'metasrc', 'mode', 'name', 'opacity', 'orientation', 'selected', 'selectedpoints', 'showlegend', 'stackgaps', 'stackgroup', 'stream', 'text', 'textfont', 'textposition', 'textpositionsrc', 'textsrc', 'texttemplate', 'texttemplatesrc', 'transforms', 'type', 'uid', 'uirevision', 'unselected', 'visible', 'x', 'x0', 'xaxis', 'xcalendar', 'xhoverformat', 'xperiod', 'xperiod0', 'xperiodalignment', 'xsrc', 'y', 'y0', 'yaxis', 'ycalendar', 'yhoverformat', 'yperiod', 'yperiod0', 'yperiodalignment', 'ysrc', 'key', 'set', 'frame', 'transforms', '_isNestedKey', '_isSimpleKey', '_isGraticule', '_bbox'
Q2
ggplot is great for static graphs that do not need to be interacted
with. it is simple, intuitive and widely used.
plotly is designed for dynamic data visualizations that allow for
user interaction. it can also create more advanced graphs like 3D plots,
animations, or maps.
you’d definitely want to use gglot2 for reports/papers that will be
printed out and cannot be accessed online for interactive use.
#Q3
library(plotly)
library(gapminder)
data(gapminder)
# Install and load necessary libraries
install.packages("plotly")
##
## The downloaded binary packages are in
## /var/folders/8_/x_11_k6n63x968_lr6dggkt80000gn/T//RtmpqTiAdv/downloaded_packages
library(plotly)
# Load the gapminder dataset
data(gapminder)
# Select data for the United States
pal <- subset(gapminder, country == "Israel")
q3 <- plot_ly(
data = pal,
x = ~year,
y = ~lifeExp,
type = "scatter",
mode = "lines+markers",
name = "Life Expectancy",
yaxis = "y",
hoverinfo = "y+name"
) %>%
add_trace(
y = ~gdpPercap,
name = "GDP per Capita",
yaxis = "y2",
hoverinfo = "y+name"
) %>%
layout(
title = "Life Expectancy and GDP per Capita (Israel)",
xaxis = list(title = "Year"),
yaxis = list(title = "Life Expectancy"),
yaxis2 = list(
title = "GDP per Capita",
overlaying = "y",
side = "right"
)
)
q3
this chart shows how life expectancy in years and GDP per Capita have
changed between 1952 and 2007. we observe the trend that both variables
have increased simultaneously suggesting a positive correlation between
the two variables. the narrative advanced by this comparison is that
economic growth might be associated with better healthcare systems that
allow people to live longer lives. It is important to note that we do
not have evidence to say that the relationship is causal.